package com.iMusic.businessLogicLayer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.iMusic.integrationLayer.DBConnection;
import com.iMusic.integrationLayer.DBConnectionManager;

public class Product {

	Integer productId = 0;
	String name = null;
	String description = null;
	String typeName = null;
	String categoryName = null;
	Float price = (float) 0.0;
	Integer unitsInStock = 0;
	String countryName = null;
	
	static private DBConnectionManager dbConnMgr = null;
	
	/*
	 * Constructor
	 */
	public Product() {
		if (dbConnMgr == null) {
			dbConnMgr = DBConnectionManager.getDBConnectionManager();
		}
	}

	/*
	 * Populates product hash based on keywords
	 */
	static public List<Product> searchByKeywords(Account account, String keywords) throws SQLException {
		
		List<Product> productList = new ArrayList<Product>();
		boolean disconnectFromDB = false;
		dbConnMgr = DBConnectionManager.getDBConnectionManager();

		DBConnection dbConn = null;
		if (account != null) {
			dbConn = account.getDBConnection();
		}
		else {
			dbConn = dbConnMgr.getDBConnection();
			dbConn.connect();
			disconnectFromDB = true;
		}
		
		String stmt = "SELECT P.ProductID, P.ProductName, P.Description, PT.TypeName, CT.CategoryName, P.Price, P.UnitsInStock, C.CountryName " +
						"FROM PRODUCT P, PRODUCTTYPE PT, CATEGORYTYPE CT, COUNTRYTYPE C " + 
						"WHERE ";
		if (keywords != null) {
			stmt += " 		(P.ProductName like '%" + keywords + "%' " + 
					"		OR P.Description like '%" + keywords + "%' " +
					"		OR PT.TypeName like '%" + keywords + "%' " +
					"		OR CT.CategoryName like '%" + keywords + "%') " +
					"		AND ";
		}

		stmt += "P.TypeID = PT.TypeID " +
				"AND P.CategoryID = CT.CategoryID " +
				"AND P.CountryID = C.CountryID " +
				"ORDER BY P.ProductName, CT.CategoryName ";

		ResultSet rs = dbConn.query(stmt);

		while (rs != null && rs.next()) {
			
			Product prod = new Product();
			prod.setProductId(rs.getInt("P.ProductID"));
			prod.setName(rs.getString("P.ProductName"));
			prod.setDescription(rs.getString("P.Description"));
			prod.setTypeName(rs.getString("PT.TypeName"));
			prod.setCategoryName(rs.getString("CT.CategoryName"));
			prod.setPrice(rs.getFloat("P.Price"));
			prod.setUnitsInStock(rs.getInt("P.UnitsInStock"));
			prod.setCountryName(rs.getString("C.CountryName"));
			
			productList.add(prod);
		}
		rs.close();
		rs = null;
		
		if (disconnectFromDB) {
			dbConn.disconnect();
			dbConnMgr.freeDBConnection(dbConn);
		}
		
		return productList;
	}	
	

	/*
	 * Populates product hash based on keywords
	 */
	static public List<Product> searchByTypeAndCategory(Account account, String productType, String category) throws SQLException {
		
		List<Product> productList = new ArrayList<Product>();
		boolean disconnectFromDB = false;
		dbConnMgr = DBConnectionManager.getDBConnectionManager();

		DBConnection dbConn = null;
		if (account != null) {
			dbConn = account.getDBConnection();
		}
		else {
			dbConn = dbConnMgr.getDBConnection();
			dbConn.connect();
			disconnectFromDB = true;
		}
		
		String stmt = "SELECT P.ProductID, P.ProductName, P.Description, PT.TypeName, CT.CategoryName, P.Price, P.UnitsInStock, C.CountryName " +
				"FROM PRODUCT P, PRODUCTTYPE PT, CATEGORYTYPE CT, COUNTRYTYPE C " +
				"WHERE P.TypeID = PT.TypeID " +
				"AND P.CategoryID = CT.CategoryID " +
				"AND P.CountryID = C.CountryID ";
		if (productType != null) {
			stmt += "AND PT.TypeName = '" + productType + "' ";
		}
		if (category != null) {
			stmt += "AND CT.CategoryName = '" + category + "' ";
		}
		stmt += "ORDER BY P.ProductName, CT.CategoryName ";
		
		ResultSet rs = dbConn.query(stmt);
		
		while (rs != null && rs.next()) {
			
			Product prod = new Product();
			prod.setProductId(rs.getInt("P.ProductID"));
			prod.setName(rs.getString("P.ProductName"));
			prod.setDescription(rs.getString("P.Description"));
			prod.setTypeName(rs.getString("PT.TypeName"));
			prod.setCategoryName(rs.getString("CT.CategoryName"));
			prod.setPrice(rs.getFloat("P.Price"));
			prod.setUnitsInStock(rs.getInt("P.UnitsInStock"));
			prod.setCountryName(rs.getString("C.CountryName"));
			
			productList.add(prod);
		}
		if (rs != null)
			rs.close();
		rs = null;
		
		if (disconnectFromDB) {
			dbConn.disconnect();
			dbConnMgr.freeDBConnection(dbConn);
		}
		
		return productList;
	}	
	
	
	
	/*
	 * Populates product hash based on keywords
	 */
	static public Product getProduct(Account account, Integer productId) throws SQLException {
		
		boolean disconnectFromDB = false;
		dbConnMgr = DBConnectionManager.getDBConnectionManager();
		Product prod = null;

		DBConnection dbConn = null;
		if (account != null) {
			dbConn = account.getDBConnection();
		}
		else {
			dbConn = dbConnMgr.getDBConnection();
			dbConn.connect();
			disconnectFromDB = true;
		}
		
		ResultSet rs = dbConn.query("SELECT P.ProductID, P.ProductName, P.Description, PT.TypeName, CT.CategoryName, P.Price, P.UnitsInStock, C.CountryName " +
									"FROM PRODUCT P, PRODUCTTYPE PT, CATEGORYTYPE CT, COUNTRYTYPE C " +
									"WHERE P.ProductID = " + productId + " " +
									"AND P.TypeID = PT.TypeID " +
									"AND P.CategoryID = CT.CategoryID " +
									"AND P.CountryID = C.CountryID " +
									"ORDER BY P.ProductName, CT.CategoryName ");
		
		if (rs != null && rs.next()) {
			
			prod = new Product();
			prod.setProductId(rs.getInt("P.ProductID"));
			prod.setName(rs.getString("P.ProductName"));
			prod.setDescription(rs.getString("P.Description"));
			prod.setTypeName(rs.getString("PT.TypeName"));
			prod.setCategoryName(rs.getString("CT.CategoryName"));
			prod.setPrice(rs.getFloat("P.Price"));
			prod.setUnitsInStock(rs.getInt("P.UnitsInStock"));
			prod.setCountryName(rs.getString("C.CountryName"));

		}
		rs.close();
		rs = null;
		
		if (disconnectFromDB) {
			dbConn.disconnect();
			dbConnMgr.freeDBConnection(dbConn);
		}
		
		return prod;
	}	
	
	
	/*
	 * Remove a product from stock
	 */
	public void removeFromStock (int quantity) {
		
		dbConnMgr = DBConnectionManager.getDBConnectionManager();
		DBConnection dbConn = dbConnMgr.getDBConnection();;
		dbConn.connect();
		
		String sqlStmt = "UPDATE Product SET UnitsInStock = UnitsInStock - " + quantity + " " +
							"WHERE ProductId = " + productId + " " +
							"AND UnitsInStock > 0";
		System.out.println(sqlStmt);
		dbConn.execSQL(sqlStmt);
		dbConn.execSQL("commit");

		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);
	}
	
	/*
	 * Restore from stock, to be used when an order is being cancelled
	 */
	public void restoreFromStock (int quantity) {
		
		dbConnMgr = DBConnectionManager.getDBConnectionManager();
		DBConnection dbConn = dbConnMgr.getDBConnection();;
		dbConn.connect();
		
		String sqlStmt = "UPDATE Product SET UnitsInStock = UnitsInStock + " + quantity + " " +
							"WHERE ProductId = " + productId;
		System.out.println(sqlStmt);
		dbConn.execSQL(sqlStmt);
		dbConn.execSQL("commit");

		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);
	}
	
	/*
	 * Getters and Setters
	 */
	public Integer getProductId() {
		return productId;
	}

	public void setProductId(Integer productId) {
		this.productId = productId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public String getTypeName() {
		return typeName;
	}

	public void setTypeName(String typeName) {
		this.typeName = typeName;
	}

	public String getCategoryName() {
		return categoryName;
	}

	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}

	public Float getPrice() {
		return price;
	}

	public void setPrice(Float price) {
		this.price = price;
	}

	public Integer getUnitsInStock() {
		return unitsInStock;
	}

	public void setUnitsInStock(Integer unitsInStock) {
		this.unitsInStock = unitsInStock;
	}

	public String getCountryName() {
		return countryName;
	}

	public void setCountryName(String countryName) {
		this.countryName = countryName;
	}
	

	
	
}
